﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.Common;
using System.Data;

namespace ServerMonitoringAssistantWF
{
    class SqliteMgr
    {
        private static SqliteMgr instance;
        public static SqliteMgr me
        {
            get
            {
                if (null == instance)
                {
                    instance = new SqliteMgr();
                }
                return instance;
            }
        }
        public string ConnectionString { get; set; }

        public DbConnection GetConnection()
        {
            if (!System.IO.File.Exists(ConnectionString))
            {
                SQLiteConnection.CreateFile(ConnectionString);
            }
            SQLiteConnectionStringBuilder connsbld = new SQLiteConnectionStringBuilder();
            connsbld.DataSource = ConnectionString;
            connsbld.Password = "";
            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = connsbld.ToString();
            return conn;
        }
        public DataTable ExecuteDataTable(string sqlstmt, DbParameter[] dbparams=null)
        {
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlstmt;
                    if (null != dbparams)
                    {
                        cmd.Parameters.AddRange(dbparams);
                    }
                    DbDataAdapter ada = new SQLiteDataAdapter();
                    ada.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    ada.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }

        public int ExecuteNoneQuery(string sqlstmt, DbParameter[] dbparams = null)
        {
            using (DbConnection conn = GetConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlstmt;
                    if (null != dbparams)
                    {
                        cmd.Parameters.AddRange(dbparams);
                    }
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public DbParameter CreateParameterInputString(string name, string value)
        {
            DbCommand cmd = new SQLiteCommand();
            DbParameter para = cmd.CreateParameter();
            para.ParameterName = name;
            para.Value = value;
            return para;
        }

        public DbParameter CreateParameterInputInteger(string name, int value)
        {
            DbCommand cmd = new SQLiteCommand();
            DbParameter para = cmd.CreateParameter();
            para.ParameterName = name;
            para.Value = value;
            return para;
        }
    }
}
